﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Data;
using System.Windows.Forms;

namespace PMSSys
{
    public class DBconnentor
    {
        private MySqlConnection MariaConnector;

        private String permissionlevel;

        public String Permissionlevel
        {
            get { return permissionlevel; }
        }

        public bool OpenConnection(String database, String host, String username, String password, String port)
        {
            

            MariaConnector = new MySqlConnection("Database=" + database + "; Server=" + host + "; Port=" + port + ";" +
                "Username=" + username + "; Password='" + password +"'");
            

            try
            {
                MariaConnector.Open();
                String permissions = "SELECT level FROM `userpermissionstable` WHERE `username`= '" + username + "'";
                permissionlevel = (String) scalarView(permissions);
                if (permissionlevel != "Secretary" && permissionlevel != "Physician") {
                    MessageBox.Show("This User is not authorized to use this program", "Authentication error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                Application.Restart();
                }
                    
                
                return true;
            }
            catch (MySqlException el)
            {
                MessageBox.Show(el.Message.ToString(), "Authentication Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return false;
            }
        }

        public bool closeConnection()
        {
            try
            {
                MariaConnector.Close();
                return true;
            }
            catch (MySqlException e)
            {
                MessageBox.Show(e.Message.ToString(), "Authentication Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return false;
            }
        }

        public MySqlConnection getConnection()
        {
            return MariaConnector;
        }
        public string lastID()
        {
            string strSQLSelect = "SELECT LAST_INSERT_ID()";
            return scalarView(strSQLSelect).ToString();
        }
        public DataTable view(String selectQueryString)
        {
            MySqlCommand selectQuery = new MySqlCommand(selectQueryString, MariaConnector);
            MySqlDataAdapter query = new MySqlDataAdapter(selectQuery);

            DataSet set = new DataSet();
            try
            {
                query.Fill(set);
                return set.Tables[0];
            }
            catch (MySqlException e)
            {
                MessageBox.Show(e.Message.ToString(), "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return null;
            }

        }
        public Object scalarView(String selectQueryString)
        {
            MySqlCommand selectQuery = new MySqlCommand(selectQueryString, MariaConnector);
            try
            {
                object tmp = selectQuery.ExecuteScalar();
                return tmp;
            }
            catch (MySqlException e)
            {
                MessageBox.Show(e.Message.ToString(), "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return null;
            }

        }
        public bool nonQueryExecute(MySqlCommand query) 
        {
            try
            {
            query.ExecuteNonQuery();
            return true;
            }
            catch (MySqlException e)
            {
                MessageBox.Show(e.Message.ToString(), "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return false;
            }
        }
        public bool delete(string deletequery)
        {
            try
            {
                MySqlCommand query = new MySqlCommand(deletequery, MariaConnector);
                return nonQueryExecute(query);
            }
            catch (MySqlException e)
            {
                MessageBox.Show(e.Message.ToString(), "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return false;
            }
        }

    }

}
